 /*=============================================================================
+++++++++++++BANGLADESH CONSUMMER PROTECTION DIGITAL SURVEY PROJECT+++++++++++++
+++++++++++++++++++++++++++++++DATA ANALYSIS+++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++Period: FEBRUARY 2021++++++++++++++++++++++++++++++
++++++++++++++++Authors: BECHIR OUEDRAOGO, RESEARCH ASSOCIATE++++++++++++++++++
+++++++++++++++++++++++++++++++Stata version: 15.0  +++++++++++++++++++++++++++
=============================================================================*/
	clear all
	set more off
	
	 
 * Install commands
	//ssc install grstyle
	//ssc install palettes
	//ssc install colrspace
	

	
	global dir "C:\Users\BOuedraogo\Box\Banglandesh_CS_Analysis"
	if "`c(username)'"=="Brian Mwesigwa" global dir "C:\Users\Brian Mwesigwa\Box\Banglandesh_CS_Analysis" // Willie uses Box Drive which uses a slightly different file path

	cd "$dir"

	global raw "$dir\1_raw"
	global do "$dir\2_do"
	global data "$dir\3_data"
	global demographics "$dir\4_output\1_demographics"
	global usage "$dir\4_output\2_usage"
	global challenges "$dir\4_output\3_challenges"
	global competition "$dir\4_output\4_competition&redress"
	global redress "$dir\4_output\5_redress"
	
	* Date and time
	loc today : di %tdCCYY.NN.DD date(c(current_date), "DMY")
	local now = substr(subinstr("`c(current_time)'", ":", "", .),1,4)	
	
*************************************************************************
***********GENERATE AN IDENTIFIER IN EACH PART OF THE DATA SET***********
*************************************************************************
	
	clear
	use "$data\bangladesh survey_with urban"
	
	keep caseid division division_name division_code district district_name ///
	district_code upazila_code upazila_oth_eng upazila_name urban
	
	ren division division_clean
	ren division_name division_name_clean 
	ren division_code division_code_clean
	ren district district_clean 
	ren district_name district_name_clean 
	ren district_code district_code_clean 
	ren upazila_code upazila_code_clean 
	ren upazila_oth_eng upazila_oth_eng_clean 
	ren upazila_name upazila_name_clean
	tempfile previous
	save `previous'
	
	
	clear
	use "$data\Consumer Protection ReSurvey_clean_Complete_only_for_Ananlysis (1)"

	merge 1:1 caseid using `previous'
	//	br if _merge==1 /// 605 matched, 4 unmatched obs to solve. Include or exclude ?

*************************************************************************
******************************SET PARAMETERS*****************************
*************************************************************************	

/********************************************************************************	
***************SECTION 1: DEMOGRAPHICS******************************************	

	Analysis:	
			Gender
			Age
			Education (categorize appropriately)
			HH status
			# of people in the household.
			Formal Employment
			Personal Monthly Income/ Household income. 

	VARIABLES: gender resp_edu  hh_members work_letter income_personal income_hh*/
		

*******GENDER**********

  * Gender repartition by living area
	preserve
	contract gender, nomiss percent(Percent) // brian has made changes here
	replace Percent=Percent/100
	cap export excel using "$demographics\genderdistribution", firstrow(variable) sheet("Country") replace
	restore
	
	preserve
	contract   division_name_clean gender , nomiss  // brian has made changes here
		cap export excel using "$demographics\genderdistribution", firstrow(variable) sheet("bydivision") sheetmod
	restore
	

***********EDUCATION**********
*graph bar, over(male) ytitle(Percent, color($ipablue)) blabel(bar, format(%9.0f)) t1title("Gender") 	
  **Create a new education Var***
	gen education_cat=.
	
	replace education_cat=1 if (resp_edu==0|resp_edu==1)
	replace education_cat=2 if (resp_edu==2|resp_edu==3) 
	replace education_cat=3 if (resp_edu==4|resp_edu==5|resp_edu==6) 
	replace education_cat=4 if (resp_edu==7|resp_edu==8|resp_edu==9 | resp_edu==10|resp_edu==11) 
	
	
	label define educ_cat 1 "No education" 2"Primary" 3"Secondary" 4"Tertiary" 
	label values education_cat educ_cat
tab education_cat

  * Education repartition by country
  
  preserve
	contract education_cat,nomiss percent(Percent) // brian made changes
	replace Percent=Percent/100
	export excel using "$demographics\educationlevels", firstrow(variable) sheet("Country") replace
	restore
     * Education repartition by division
	preserve
	contract division_name_clean education_cat ,nomiss  // Brian made changes here
	export excel using "$demographics\educationlevels", firstrow(variable) sheet("bydvision") sheetmod
	restore
	
		
	//graph bar , over(education_cat) ascategory asyvars ytitle(Percent, color($ipablue)) blabel(bar, format(%9.0f)) t1title("Education Levels") nofill by(division_name)
	//graph save "$demographics\Education repartition by division", replace
	
 * Education repartition by gender
	preserve
	contract education_cat gender ,nomiss 
	export excel using "$demographics\educationlevels", firstrow(variable) sheet(bygender) sheetmod 
	restore
	
		
**Urbanity

preserve
	contract urban,nomiss percent(Percent)
	replace Percent=Percent/100
	cap export excel using "$demographics\urbanity", firstrow(variable) replace sheet("urbanity")	
restore


**
	
******************AGE*****************

  *Create categories for age**
	
	recode age_respondent (18/24 = 1) (25/44=2) (45/100 = 3), gen(age_range)
	lab def age_range 1 "18-24" 2 "25-44" 3 "45+", modify
	lab val age_range age_range
	order age_range, a(age_respondent)
	
 *Age repartition by division
	preserve
	contract age_range,nomiss percent(Percent)
	replace Percent=Percent/100
	cap export excel using "$demographics\agedistribution", firstrow(variable) replace sheet("country")	
	restore
	//graph bar, over(age_range) asyvars ytitle(Percent, color($ipablue)) blabel(bar, format(%9.0f)) 	
	//graph save "$demographics\Age repartition at the country level", replace
 
 *Age repartition by division //Brian made some changes
	preserve
	contract division_name_clean age_range ,nomiss 
	cap export excel using "$demographics\agedistribution", firstrow(variable) sheet("bydivision")	sheetmod
	restore
	//graph bar, over(division_name_clean) over(age_range) asyvars ytitle(Percent, color($ipablue)) blabel(bar, format(%9.0f)) 	
	//graph save "$demographics\Age repartition", replace

	
******************PERSONAL INCOME*****************	

	lab def incomerange 0 "No response" 1 "Less than 2,000" 2"2,001 - 6,000" 3 "6,001 - 10,000" 4 "10,001 - 14,000" 5 "14,001 - 20,000" 6 "20,001 - 40,000" 7 "40,001 - 60,000" 8 "60,001 - 80,000" 9 "80,001 - 100,000" 10 "100,001 - 120,000" 11 "More than 120,000" -999"Ne sait pas"
	gen income=income_personal

	gen incomepersonal=.
	replace incomepersonal=0 if income==0
	replace incomepersonal=1 if income<2000
	replace incomepersonal=2 if income>2000 & income<=6000
	replace incomepersonal=3 if income>6000 & income<=10000
	replace incomepersonal=4 if income>10000 & income<=14000
	replace incomepersonal=5 if income>14000 & income<=20000
	replace incomepersonal=6 if income>20000 & income<=40000	
	replace incomepersonal=7 if income>40000 & income<=60000		
	replace incomepersonal=8 if income>60000 & income<=80000
	replace incomepersonal=9 if income>80000 & income<=100000				
	replace incomepersonal=10 if income>100000 & income<=120000
	replace incomepersonal=-999 if income==-999
	
	* Relabel
	lab val incomepersonal incomerange
	
  *Income distribution country
	preserve 
	contract incomepersonal, nomiss percent(Percent)
	replace Percent=Percent/100
	export excel using "$demographics\incomedistribution", firstrow(variable) sheet("personalincome") replace
	restore
	
******************HOUSEHOLD INCOME*****************	

	lab def incomerangeh 0 "No response" 1 "Less than 5,000" 2"5,001 - 8,000" 3 "8,001 - 11,000" 4 "11,001 - 14,000" 5 "14,001 - 17,000" 6 "17,001 - 20,000" 7 "20,001 - 26,000" 8 "26,001 - 30,000" 9 "30,001 - 50,000" 10 "50,001 - 100,000" 11 "More than 100,000" -999"Ne sait pas"
	gen incomeh=income_hh

	gen incomemenage=.
	replace incomemenage=1 if incomeh<5000
	replace incomemenage=2 if incomeh>5000 & incomeh<=8000
	replace incomemenage=3 if incomeh>8000 & incomeh<=11000
	replace incomemenage=4 if incomeh>11000 & incomeh<=14000	
	replace incomemenage=5 if incomeh>14000 & incomeh<=17000		
	replace incomemenage=6 if incomeh>17000 & incomeh<=20000		
	replace incomemenage=7 if incomeh>20000 & incomeh<=23000	
	replace incomemenage=7 if incomeh>20000 & incomeh<=26000				
	replace incomemenage=8 if incomeh>26000 & incomeh<=30000	
	replace incomemenage=9 if incomeh>30000 & incomeh<=50000	
	replace incomemenage=10 if incomeh>50000 & incomeh<=100000							
	replace incomemenage=11 if incomeh>100000 
	replace incomemenage=-999 if incomeh==999
	
	label value incomemenage incomerangeh
	
	preserve 
	contract incomemenage, nomiss percent(Percent)
	replace Percent=Percent/100
	export excel using "$demographics\incomedistribution", firstrow(variable) sheet("householdincome") sheetmod
	restore

******************FORMAL SECTOR JOB*****************		
/*
	graph pie, over(work_letter) ///
	pie(1, color(black%20)) pie(2, explode) ///
	plabel(1 percent, size(medlarge) format(%9.0f)) plabel(2 percent, size(medlarge) format(%9.0f)) ///
	legend(order(2 1)) 
	graph save "$demographics/formal sector jobs", replace
	*/	
	preserve 
	contract work_letter, nomiss percent(Percent)
	replace Percent=Percent/100
	export excel using "$demographics\formal_employment", firstrow(variable) replace
	restore
		
******************Household composition******************

  *Relationship to HH head x gender
	* Recode other specify
	replace hhh_relation=2 if hhh_relation_oth_eng=="Father"
	replace hhh_relation=4 if hhh_relation_oth_eng=="Grand Children of the HH chief"	
	
	* Generate status variable
	gen hhstatus = .
	replace hhstatus=1 if headhousehold==1  //Head
	replace hhstatus=2 if hhh_relation==1 //Spouse of the head
	replace hhstatus=4 if hhh_relation==4 | hhh_relation==6
	replace hhstatus=3 if hhh_relation!=1 & hhh_relation!=.  & hhh_relation!=4 & hhh_relation!=6
	
	label define hhstatus 1"Head" 2"Spouse" 3"Other" 4"Children of HH"
	label value hhstatus hhstatus
   
	//graph hbar, over(hhstatus) over(gender, label(labcolor("${ipablue}"))) asyvars stack ///
		//blabel(bar, size(medium) position(center) format(%9.0f)) ///
		//ytitle(Percent, color($ipablue)) legend(order(3 2 1) cols(1))
	   
	   preserve
	    contract hhstatus gender, nomiss percent(Percent)
			    export excel using "$demographics\hh_composition", firstrow(variable) sheet("Householdhead") replace
	    restore
		
		
***what do consumer often use their phones for? 
preserve
keep caseid phn_purpose_* gender
reshape long phn_purpose_, i(caseid) j(purpose)
label define purpose 1"Personal use" 2"Businees use" 
label val purpose purpose

collapse (mean) mean=phn_purpose_, by(gender purpose)
export excel using "$demographics\phonesusedfor", firstrow(var) replace
restore

**bygender
preserve
keep caseid phn_purpose_* gender
reshape long phn_purpose_, i(caseid) j(purpose)
label define purpose 1"Personal use" 2"Businees use" 
label val purpose purpose

collapse (mean) mean=phn_purpose_, by( gender purpose)


export excel using "$demographics\phonesusedfor", firstrow(var) sheet(bygender) sheetmodify
restore

********************************************************************************
**********************SECTION 2: ACCESS AND USAGE*******************************
/*

	* Phone ownership, what phones are used for
	* Debit/credit card ownership
	* DFS usage by Service type (MM, MB, MA, ML)
	* Mobile Money Usage
		-Mobile money providers used in the last 90 days/ used most often. 
		-What do they use mobile money for
		-Segment mobile money usage by consumer segments 
	* Mobile Banking  Usage
		-Mobile banking providers are ever/most used. 
		-Segment usage by consumer segments 
	
	* Mobile loan Usage
		-Providers ever used/ used last 90 days
		-What loan was used for
		-Segment loan use by consumer segment. 
		-Have more than one loan
		-Unable to repay loan (on time and failed completed)
	
	* Agent use
		-How many agents used/ visits made in the last 30 days
		-What they used the agents for. */


**********************Phone ownership -- sharing, phone type********************
		
  //Phone ownership
	* Phone ownership
	label define own 1"Own a mobile phone" 0"Do not own a mobile phone"
	gen phone_own=.
	replace phone_own=1 if phn_own==1
	replace phone_own=0 if phn_own==0
	label value phone_own own
	preserve
	contract phone_own, nomiss percent(Percent)
	replace Percent=Percent/100
 export excel using "$usage\phone_ownership", firstrow(variable) sheet("Phoneownership") replace
	restore
	
	* Phone shareing
	label define share 0"Do not share the phone with someonne" 1"Share the phone with someone"
	gen phone_share=.
	replace phone_share=1 if phn_use==1
	replace phone_share=0 if phn_use==0	
	label value phone_share share
	preserve
	contract phone_share, nomiss percent(Percent)
	replace Percent=Percent/100
	export excel using "$usage\phone_ownership", firstrow(variable) sheet("Phonesharing") sheetmod
	restore
	
  //Debit/credit card ownership
	label define card 0"Do not own a debit or credit card" 1"Own a debit or credit card"
	gen card_debit_credit=.
	replace card_debit_credit=0 if debit_credit==0
	replace card_debit_credit=1 if debit_credit==1
	label value card_debit_credit card
	preserve
	contract card_debit_credit, nomiss percent(Percent)
	replace Percent=Percent/100
	export excel using "$usage\credit_card_ownership", firstrow(variable) replace
	restore

  //DFS usage by service type (over, 90 days) of MM, MB, ML, Agents
	* Mobile money
	gen mm=.
	replace mm=1 if mobprov_use==1
	replace mm=0 if mobprov_use==0
	replace mm=-88 if mobprov_use==-88
	label define mm 1"Used in the last 90 days" 0"Not used in the last 90 days" -88"Refused to answer"
	label value mm mm
	
	* Mobile banking // Brian made some changes here. 
	gen mb=.
	replace mb=1 if mob_bank_trans==1
	replace mb=0 if mob_bank_trans==0
	replace mb=-88 if mob_bank_trans==-88
	label value mb mm
	
	* Mobile loan
	//gen ml=.
	//replace ml=1 if ml_last90==1
	//replace ml=0 if ml_last90==0
	//replace ml=-88 if ml_last90==-88
	//label value ml mm 
	
	/* No one has taken a mobile loan so we can not compute*/
	
	
	* Agent money
	gen am=.
	replace am=1 if fin_trans_agent==1
	replace am=0 if fin_trans_agent==0
	replace am=-88 if fin_trans_agent==-88	
	label value am mm
	
	preserve
	contract mm, nomiss percent(Percent)
	replace Percent=Percent/100
	export excel using "$usage\dfs_usage", firstrow(varl) sheet("mobilemoney") replace
    restore
	
	preserve
	contract mb, nomiss percent(Percent)
	replace Percent=Percent/100
	export excel using "$usage\dfs_usage", firstrow(varl) sheet("mobilebanking") sheetmod
    restore
	
	//preserve
	//contract ml, nomiss percent(Percent)
	//replace Percent=Percent/100
	//export excel using "$usage\Mobile_money_service", firstrow(varl) sheet("mobile loan") sheetmod
    //restore
	
	preserve
	contract am, nomiss percent(Percent)
	replace Percent=Percent/100
	export excel using "$usage\dfs_usage", firstrow(varl) sheet("agentbanking") sheetmod
    restore
	*/
	
  //Mobile Money Usage
	*Mobile money providers used in the last 90 days/ used most often. 
	
	
	**Brian making changes here**** Mobile money providers ever used versus mobile money providers used in the last 90 days/
	
preserve
	keep caseid ever_mobprov_* mobprov_list_* division_name_clean
	reshape long ever_mobprov_ mobprov_list_, i(caseid) j(provider)
	
	lab def providers 1"Bkash" 2"Rocket" 3"SureCash" 4"Naga" 5"Upay" -96"other"
	drop if ever_mobprov_==. & mobprov_list_==.
	label val provider providers
	contract provider ever_mobprov_, nomiss
	keep if ever_mobprov_==1
	export excel using "$usage\mobile_moneyproviderused", firstrow(varl) sheet("provideeverused")replace
restore
	
preserve
	keep caseid ever_mobprov_* mobprov_list_* division_name_clean
	reshape long ever_mobprov_ mobprov_list_, i(caseid) j(provider)
	
	lab def providers 1"Bkash" 2"Rocket" 3"SureCash" 4"Naga" 5"Upay" -96"other"
	drop if ever_mobprov_==. & mobprov_list_==.
	label val provider providers
	contract provider mobprov_list_, nomiss
	keep if mobprov_list_==1
	export excel using "$usage\mobile_moneyproviderused", firstrow(varl) sheet("usedinlast90days")sheetmod
	
restore

		
************What do they use mobile money for
preserve

        keep caseid reason_use_prov_*
		label define reasonuseprov 1 "Send Money" 2"Recieve money" 3"Recieve Salary" 4"Gov't money" 5" Recieve Business payments" 6"Make business payments" 7"pay bills"  8"save money" 9"Buy airtime" 
		
 reshape long reason_use_prov_, i(caseid) j(reasonuseprov)
 label values reasonuseprov reasonuseprov
 collapse (mean) reason_use_prov_, by(reasonuseprov)
	
		export excel using "$usage\mobileusedfor", firstrow(varl) replace
		restore
		
	*Segment mobile money usage by consumer segments
		* We will segment by consumer AGE, GENDER and INCOME
		//age_range incomemenage incomepersonal education_cat

		
			preserve
			* Manage income vars
			replace incomemenage=. if incomemenage==0
			xtile incomemenage_cat = incomemenage, n(3)
			
			replace incomepersonal=. if incomepersonal==0
			xtile incomepersonal_cat = incomepersonal, n(3)
			
			gen incomemenage_catt="Low Income" if incomemenage_cat==1
			replace incomemenage_catt="Middle Income" if incomemenage_cat==2
			replace incomemenage_catt="High Income" if incomemenage_cat==3
			
			gen incomepersonal_catt="Low Income" if incomepersonal_cat==1
			replace incomepersonal_catt="Middle Income" if incomepersonal_cat==2
			replace incomepersonal_catt="High Income" if incomepersonal_cat==3
			
			drop incomepersonal_cat incomemenage_cat
			ren incomemenage_catt incomemenage_cat
			ren incomepersonal_catt incomepersonal_cat
			
	**Brian made changes here
	
// convert to string
	foreach var of varlist gender incomepersonal_cat education_cat age_range urban {
		cap decode `var', gen(temp)
		if _rc == 0 {
			drop `var'
			ren temp `var'
		}
		ren `var' cut_`var'

	}

	// prep outcome vars
	
		
	gen i = _n
	
	tempfile reshaped reshaped
	
	reshape long cut_, i(i) j(cuttype) string // reshape to case-cut level
    
	save `reshaped'
	
	
  drop i 
**# Bookmark #3
	ren cut_ cut
	drop if cut==""
	
	*Brian made some changes here, I also included urbanity in the cuttypes**
	
	replace cuttype = "Age" if cuttype == "age_range"
	replace cuttype = "Education" if cuttype == "education_cat"
	replace cuttype = "Gender" if cuttype == "gender"
	replace cuttype = "Income" if cuttype == "incomepersonal_cat"
	

	* Cut type mobile banking
use `reshaped', clear
	**brians new code** going to replicate for other cuts
	collapse (mean) mean=mb (sd) sd=mb (count) n=mb, by(cuttype cut_)
	gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
	gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
	keep cuttype cut_ mean sd n lo hi
	*keep cuttype cut mb
	*order cuttype cut mb
	*contract cuttype cut mb, nomiss
	export excel using "$usage\mobile_bankingbysegment", firstrow(varl) sheet("mobilebanking_cutsegment") replace

	
	* Cut type mobile money
use `reshaped', clear
	collapse (mean) mean=mm (sd) sd=mm (count) n=mm, by(cuttype cut_)
	gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
	gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
	keep cuttype cut_ mean sd n lo hi
	export excel using "$usage\mobile_moneybysegment", firstrow(varl) sheet("mobilemoney_cutsegment") sheetmodify
	
	
	* Cut type agent
use `reshaped', clear
	collapse (mean) mean=am (sd) sd=am (count) n=am, by(cuttype cut_)
	gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
	gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
	keep cuttype cut_ mean sd n lo hi
	export excel using "$usage\mobile_agentbysegment", firstrow(varl) sheet("mobileagent_cutsegment") sheetmodify
		


** cut type credit card ownership
	
use `reshaped', clear
collapse (mean) mean=card_debit_credit (sd) sd=card_debit_credit (count) n=card_debit_credit,by(cuttype cut_)
	
	gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
	gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
	keep cuttype cut_ mean sd n lo hi
	drop if cut_==""
export excel using "$usage\creditcardbysegment", firstrow(varl) sheet("ownershipbycutsegment") replace
	

**account openning shopping around by consumer segment 


use `reshaped', clear
collapse (mean) mean=shopped_prov (sd) sd=shopped_prov (count) n=shopped_prov,by(cuttype cut_)
	
	gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
	gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
	keep cuttype cut_ mean sd n lo hi
	drop if cut_==""
export excel using "$competition\acctshoppingbycut", firstrow(varl) replace

**who is likely to respond to scams***

use `reshaped', clear
collapse (mean) mean=scam_action (sd) sd=scam_action (count) n=scam_action,by(cuttype cut_)
	
	gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
	gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
	keep cuttype cut_ mean sd n lo hi
	drop if cut_==""
export excel using "$challenges\resp2scambycut", firstrow(var) replace


**who is likely to lose money


use `reshaped', clear
collapse (mean) mean=scam_lostmoney (sd) sd=scam_lostmoney (count) n=scam_lostmoney,by(cuttype cut_)
	
	gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
	gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
	keep cuttype cut_ mean sd n lo hi
	drop if cut_==""
export excel using "$challenges\scamlostmoneybycut", firstrow(varl) replace


restore

	
*********mobile banking usage**********************************************************************************
	
			
preserve
	keep caseid bank_use_* division_name_clean bank_list
	reshape long bank_use_, i(caseid) j(provider)
lab def providers 1"Dutch Bangla Bank Ltd" 2	"Brac Bank Ltd" 3 "Islami Bank Bangladesh Ltd" 4"Mercantile Bank Ltd." 5	"United Commercial Bank Ltd."6	"Prime Bank Limited." 7	"Trust Bank" 8	"National Credit and Commerce Bank Limited" 9	"Bank Asia Limited." 10	"Dhaka Bank" 11	"AB Bank" 12	"South East Bank" 13 "First Security Islami Bank" 14 "Bangladesh Commerce Bank"15	"Standard Bank" 16	"United Commerce Bank" 17 "City Bank" 18 "Rupali Bank" -96 "other"

	drop if bank_use_==.  & bank_list==.
	label val provider providers
	collapse (mean) bank_use_, by(provider)
	
	
	*contract provider bank_use_, nomiss
	*keep if bank_use_==1
	*export excel using "$usage\mobile_bankingproviderused", firstrow(varl) sheet("provideeverused")replace
restore

preserve

contract bank_list, nomiss
export excel using "$usage\mobile_bankingproviderused", firstrow(varl) sheet("provusedmostin90days")sheetmod
restore


**reason for not using agent service or personal mobile money
preserve
keep caseid reasons_agent_* division_name_clean
reshape long reasons_agent_, i(caseid) j(reason)

lab def reason 1 "Not sure what it is" 2 "No mobile phone" 4 "Network outages" 5 "Confusing Menus" 6 "No documentation"  7 "High fees" 8 "heard bad things about DFS" 9	"Fear scams" 10	"Don't trust providers" 11 "Ever lost money" 12 "It's too new" 13 "Poor customer care" 14"Don't have to send/recieve" 15"Don't see the use" 16"Never tried" 17"Prefer cash"
lab val reason reason
contract reasons_agent_ reason, nomiss
keep if reasons_agent_==1
drop reasons_agent_
export excel using "$usage\whynotuseagentormm", firstrow(varl) sheet("reasons")replace
restore

**what they use mobile money for
preserve
keep caseid reason_use_prov_* division_name_clean
reshape long reason_use_prov_, i(caseid) j(mmusedfor)
label define mmusedfor 1 "Send money to friends/family" 2 "Receive money from friends or family" 3 "Receive salary" 4 "Receive money from government or charity" 5	"Receive payments for business" 6 "Make payments for business" 7 "Pay bills/purchase items" 8	 "Save or keep money" 9 "Buy airtime" 
label val mmusedfor mmusedfor

collapse (mean) reason_use_prov_, by(mmusedfor)

*contract reason_use_prov_ mmusedfor, nomiss percent(Percent)
*keep if reason_use_prov_==1
*drop reason_use_prov_
export excel using "$usage\mmusedfor", firstrow(varl) sheet("mmusedfor")replace
restore

** last time they did a mobile money transaction 
preserve
contract prov_last_use, nomiss percent(Percent)
export excel using "$usage\lasttimemmtrans", firstrow(varl) sheet("lastmmtrans")replace
restore


**last transaction conducted
preserve
contract prov_trans, nomiss percent(percent)
export excel using "$usage\lasttranstype", firstrow(var) sheet(lastransaction) replace
restore

**what do they use agents for***
preserve
keep caseid agent_use_* division_name_clean
reshape long agent_use_, i(caseid) j(agentsusedfor)
label define agentsusedfor 1 "Send money" 2	"Receive money" 3 "Cash out money" 4	"Deposit money" 5 "Pay bill" 6 "Buy airtime" -96 "Others"

collapse (mean) agent_use_,by(agentsusedfor)

label val agentsusedfor agentsusedfor
*contract agent_use_ agentsusedfor, nomiss percent(Percent)
*keep if agent_use_==1
*drop agent_use_


export excel using "$usage\agentsusedfor", firstrow(varl) sheet("agentsusedfor") replace
restore

**what do they use mobile banking for
preserve
keep caseid reason_bank_use_* division_name_clean
reshape long reason_bank_use_, i(caseid) j(mbusedfor)
label define mbusedfor 1 "A friend/family member uses this service" 2"Person I am sending money to/ receiving money from uses this service" ///
3"Only provider my closest agent offers" 4"Least expensive provider (lowest fees) my agent offers" 5"I tried this provider first" ///
6"This provider is reputable/ I trust this provider" 7"Linked to my bank account / salary payment" 8"This is the only provider I know" ///
9"Some transactions are now free" 10"Interface is easy to use" -96 "Others"

label val mbusedfor mbusedfor
collapse (mean) reason_bank_use_, by(mbusedfor)
stop
*contract reason_bank_use_ mbusedfor, nomiss percent(Percent)
*keep if reason_bank_use_==1
*drop reason_bank_use_
export excel using "$usage\mobile_bankingusedfor", firstrow(varl) sheet("mobilebanking_usedfor") replace
restore


*****Brian ended here** /what are DFSP usedfor next, agents used in the last 30 days


	
	
		
*********************************************************************************************	
/*********************************************** CHALLENGES**********************************/

/*			Section 3: -Challenges experienced  
					   -Most common challenges
					   -Challenges segmented by service type
					   -Challenge segmented by consumer segment
					   -Based on the prominent trends above, you can look at how the different challenges are distributed for that segment. 
					   -Excess agent fees
					   -Phishing scams by consumer segment 
					   -When the scams happen
					   -What did the scammers ask for
					   -Channel used by scammers
					   -How consumer responded
					   -How did consume know it is a scam
					   -Experienced financial loss due to scam, how much was lost? */
					   

********************What did the scammers ask for


**types of scams
preserve
contract scam_moneyaccount, nomiss percent(percent)
export excel using "$challenges\scamtype",firstrow(var) sheet("askingformoney") replace
restore
preserve
contract scam_prodservice, nomiss percent(percent)
export excel using "$challenges\scamtype",firstrow(var) sheet("providingserivce") sheetmodify
restore

		* Create variable to be outputed
		preserve
		keep caseid scam_request_*
		label define scam_request 1"scam_request" 2"Share personal  information" 3"Reverse payment" 4"Password/PIN" 5"Other account details" 6"Help friend/family" 7"Offered to send money" 8"Sell me a loan" 9"Health Care Product" 10"sell me product" 11"Sell me insurance" 12"Investement opportunity" 13 "Prize" 
		reshape long scam_request_, i(caseid) j(scam_request)
		label values scam_request scam_request
								
		collapse (mean) scam_request_, by(scam_request)	
		export excel using "$challenges\scam_request", firstrow(variables) sheet("Scam request")replace
		restore
	
		
********************When the scams happen
		preserve
		gen scamtime_label="Last month" if scam_time==1|scam_time==2|scam_time==3|scam_time==4
		replace scamtime_label="Three months ago" if scam_time==5|scam_time==6
		replace scamtime_label="More than three months" if scam_time==7|scam_time==8
		replace scamtime_label="Refused to answer" if scam_time==-88
		contract scamtime_label, nomiss percent(Percent)			
		export excel using "$challenges\scamtime", firstrow(varl) sheet("Scam time")replace
		restore
				
********************Channel used by scammers
		preserve
		keep caseid scam_channel_*
		label define channel 1"Phone call" 2"SMS" 3"Phone call and SMS"
		
		reshape long scam_channel_, i(caseid) j(channel)
		label val channel channel 
				
		collapse (mean) scam_channel_, by(channel)					
		export excel using "$challenges\scam_channel", firstrow(varl) sheet("Scam channel")replace
		restore
	
************how many consumers responded to scams
preserve
contract scam_action, nomiss percent(percent)
export excel using "$challenges\respondedtoscam", firstrow(varl) replace
restore

		
********************How consumer responded
		preserve
		
		keep caseid scam_response_*
		rename scam_response__96 scam_response_96
		
		label define scamresponse 1"Complied" 2"Declined offer" 3"Exposed Scam" 4"Asked questions" 96"Other"
		
		reshape long scam_response_, i(caseid) j(scamresponse)
		lab values scamresponse scamresponse
		collapse (mean) scam_response_, by(scamresponse)
												
		export excel using "$challenges\answer_toscammers", firstrow(varl) sheet("Answer to scammer")replace
		restore	
	
	
********************How did consumer know it is a scam
		preserve
		
		* Create variable to be outputed
		keep caseid scam_identity_*
		rename scam_identity__96 scam_identity_96
		label define scamidentity 1"Regular number" 2"Poor English" 3"Requests Personal Information" 4"Unusual time" 5"No transactions" 6"Never used service" 7"Incorrectly identified me"  8"From other people's experiences" 9"Information compaingns" 96"Other"
		reshape long scam_identity_, i(caseid) j(scamidentity)
		label val scamidentity scamidentity
		collapse (mean) scam_identity_, by(scamidentity)
								
		export excel using "$challenges\aware_scammers", firstrow(varl) sheet("Aware it is scammer")replace
		restore

********************Phishing scams by consumer segment 

	preserve
	use `reshaped', clear
	*Gen a variable considering that the respondent experienced scam
	*gen phishing=1 if chal_q1==1|chal_q2==1|chal_q3==1|chal_q4==1|chal_q5==1| ///
	*chal_q6==1|chal_q7==1|chal_q8==1|chal_q9==1
	*replace phishing=0 if phishing==.
	gen phishing =1 if scam_moneyaccount==1 | scam_prodservice==1
	replace phishing=0 if phishing==.
	
	collapse (mean) mean=phishing (sd) sd=phishing (count) n=phishing, by(cuttype cut_)
	gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
	gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
	keep cuttype cut_ mean sd n lo hi
	export excel using "$challenges\phishing_segmented2", firstrow(varl) sheet(phishing_segmented) replace
	restore

********************Experienced financial loss due to scam, how much was lost?

	
***lostmoneydue to scam***
preserve
contract scam_lostmoney, nomiss percent(percent)
export excel "$challenges\lostmoneyscam", firstrow(variables) replace
restore
**how much was lost due to scams

gen scam_lostmoney_cat=.
replace scam_lostmoney_cat=1 if scam_lostmoney_amt<=5000
replace scam_lostmoney_cat=2 if scam_lostmoney_amt>5000 & scam_lostmoney_amt<=10000
replace scam_lostmoney_cat=3 if scam_lostmoney_amt>10000 & scam_lostmoney_amt!=.

label define scamamount 1"<=5000" 2">5000 & <=10000" 3">10000"
label val scam_lostmoney_cat scamamount
tab scam_lostmoney_cat
preserve
contract scam_lostmoney_cat, nomiss percent(percent)
export excel "$challenges\lostmoneyscamamount", firstrow(variables) replace
restore

***scam money loss segmented by consumer response

preserve

keep caseid scam_response_* scam_lostmoney
label define response 1 "Complied" 2"Declined" 3"Exposed" 4 "Asked questions" 96"Other"
rename scam_response__96 scam_response_96
reshape long scam_response_, i(caseid) j(response)
label val response response
collapse (mean) scam_lostmoney, by(response scam_response_)
keep if scam_response_==1
export excel "$challenges\scamresponseandmoneyloss", firstrow(variables) replace

restore
	

****Brian working starts work on challenges*************************************************************
********************************************************************************************************

**Most common challenges********
preserve
keep caseid chal_q* gender
label define chal 1"Agent didn't keep information" 2"Agent charge me extra" 3"Adde extra fees" 4"Money was missing" 5" Difficulty with shortcode" 6"Incorrently sent money" 7"Unexpected fees" 8"Poor quality CC" 9"Couldn't figure out how to reach CC"
reshape long chal_q, i(caseid) j(chaltype)
label val chaltype chal
collapse (mean) chal_q, by(chaltype)


export excel using "$challenges\experienced_challenges", firstrow(variables) sheet("Commonchallenges") replace
restore

**challenges by service type

*MOBILE BANKING: mob_bank_trans 
*MOBILE MONEY USE: ever_mobprov
*MOBILE AGENT USE: agent_use

 gen mbuse=1 if mob_bank_trans==1
 replace mbuse=0 if mob_bank_trans!=1
 
 gen mmuse=1 if !mi(ever_mobprov)
 replace mbuse=0 if mi(ever_mobprov)

 gen mause=1 if !mi(agent_use)
 replace maus=0 if mi(agent_use)
 
preserve
keep caseid chal_q* mbuse mmuse mause
 
label define chal 1"Agent didn't keep information" 2"Agent charge me extra" 3"Adde extra fees" 4"Money was missing" 5" Difficulty with shortcode" 6"Incorrently sent money" 7"Unexpected fees" 8"Poor quality CC" 9"Couldn't figure out how to reach CC"
reshape long chal_q, i(caseid) j(chaltype)
label val chaltype chal
collapse (mean) chal_q, by(mbuse chaltype)
drop if mbuse==0
export excel using "$challenges\experienced_challenges", firstrow(variables) sheet("mobbankingchallenges") sheetmod
restore

preserve

keep caseid chal_q* mbuse mmuse mause
 
label define chal 1"Agent didn't keep information" 2"Agent charge me extra" 3"Adde extra fees" 4"Money was missing" 5" Difficulty with shortcode" 6"Incorrently sent money" 7"Unexpected fees" 8"Poor quality CC" 9"Couldn't figure out how to reach CC"
reshape long chal_q, i(caseid) j(chaltype)
label val chaltype chal
collapse (mean) chal_q, by(mmuse chaltype)
drop if mmuse==0 | mmuse==.
export excel using "$challenges\experienced_challenges", firstrow(variables) sheet("mobmoneychallenges") sheetmod

restore


preserve

keep caseid chal_q* mbuse mmuse mause
 
label define chal 1"Agent didn't keep information" 2"Agent charge me extra" 3"Adde extra fees" 4"Money was missing" 5" Difficulty with shortcode" 6"Incorrently sent money" 7"Unexpected fees" 8"Poor quality CC" 9"Couldn't figure out how to reach CC"
reshape long chal_q, i(caseid) j(chaltype)
label val chaltype chal
collapse (mean) chal_q, by(mause chaltype)
drop if mause==0
export excel using "$challenges\experienced_challenges", firstrow(variables) sheet("mobagentchallenges") sheetmod

restore


***challenges segmented by consumer segment****


			preserve
			* Manage income vars
			replace incomemenage=. if incomemenage==0
			xtile incomemenage_cat = incomemenage, n(3)
			
			replace incomepersonal=. if incomepersonal==0
			xtile incomepersonal_cat = incomepersonal, n(3)
			
			gen incomemenage_catt="Low Income" if incomemenage_cat==1
			replace incomemenage_catt="Middle Income" if incomemenage_cat==2
			replace incomemenage_catt="High Income" if incomemenage_cat==3
			
			gen incomepersonal_catt="Low Income" if incomepersonal_cat==1
			replace incomepersonal_catt="Middle Income" if incomepersonal_cat==2
			replace incomepersonal_catt="High Income" if incomepersonal_cat==3
			
			drop incomepersonal_cat incomemenage_cat
			ren incomemenage_catt incomemenage_cat
			ren incomepersonal_catt incomepersonal_cat
			
***generating variable for having experienced any challenge****

gen anychallenge =1 if big_chal!=10
replace anychallenge=0 if big_chal==10

// convert to string
	foreach var of varlist gender incomepersonal_cat education_cat age_range urban {
		cap decode `var', gen(temp)
		if _rc == 0 {
			drop `var'
			ren temp `var'
		}
		ren `var' cut_`var'

	}

	// prep outcome vars
	
		
	gen i = _n
	
	tempfile reshaped reshaped
	
	reshape long cut_, i(i) j(cuttype) string // reshape to case-cut level
    
	
	
  drop i 
**# Bookmark #3
	ren cut_ cut
	drop if cut==""
	/*
	replace cuttype = "Age" if cuttype == "age_range"
	replace cuttype = "Education" if cuttype == "education_cat"
	replace cuttype = "Gender" if cuttype == "gender"
	replace cuttype = "Income of the head" if cuttype == "incomepersonal_cat"
	replace cuttype = "Income of the household" if cuttype == "incomemenage_cat"
	*/ 
	*Brian made some changes here, I also included urbanity in the cuttypes**
	
	replace cuttype = "Age" if cuttype == "age_range"
	replace cuttype = "Education" if cuttype == "education_cat"
	replace cuttype = "Gender" if cuttype == "gender"
	replace cuttype = "Income" if cuttype == "incomepersonal_cat"
	
	collapse (mean) mean=anychallenge (count) n=anychallenge  (sd) sd=anychallenge, by(cuttype cut)


    gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
	gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
	keep cuttype cut mean sd n lo hi
	
export excel using "$challenges\challenge_segmented", firstrow(varl) sheet(challenge_segmented) replace
	restore



*********************************************************************************
/******************************REDRESS PROCESS***********************************
		Section 5: Redress process
					-Biggest challenge
					-How much was lost in the biggest challenge, how they become aware & who they think is responsible for it? 
					-How they tried to resolve the biggest challenge
					-Segment action taken by consumer segment
					-Channel used to resolve challenge
					-What was the result of the action
					-Segment result of action by channel used
					-Segment usage pattern based on outcome of action taken (resolved/unresolved)
					-Segment outcome based on amounts lost*/

****************Biggest challenge
		preserve
		keep if big_chal!=10
		contract big_chal, nomiss percent(Percent)
		export excel "$redress\biggest_challenge", firstrow(variables) replace
		restore
		
		
		
***When did biggest challenge occur

preserve
		contract bigchal_when, nomiss percent(Percent)
		export excel "$redress\biggest_chal_when", firstrow(variables) replace
restore 


****************How much was lost in the biggest challenge, how they become aware & who they think is responsible for it? 

		preserve
		gen loss_cat= . 
		replace loss_cat=1 if bigchal_lossamt<=3000
		replace loss_cat=2 if bigchal_lossamt >3000 & bigchal_lossamt<=6000
		replace loss_cat=3 if bigchal_lossamt >6000 & bigchal_lossamt!=.
		
		label define loss 1"<=3000" 2">3000 & <=60000" 3 ">60000"
		lab val loss_cat loss
		contract loss_cat, nomiss percent(Percent)
		replace Percent=Percent/100
		export excel "$redress\bigamountlost", firstrow(variables) replace
		restore

	
***************How they tried to resolve the biggest challenge
preserve
		keep caseid bigchal_resolve_*	
		rename bigchal_resolve__96 bigchal_resolve_96
		label define response 0"No Action" 1"Contacted agent" 2"Contacted provider" 3"Contacted local authorities" 4"Stopped use" 5"Asked friend" 6"Contacted regulatory authority" 96"Other"
		reshape long bigchal_resolve_, i(caseid) j(bigresponse)
		label val bigresponse response
		
			
		collapse (mean) bigchal_resolve_, by(bigresponse)
		
		export excel "$redress\resolve_challenge", firstrow(variables) replace
restore

***************Segment action taken by consumer segment
			

			preserve
			* Manage income vars
			replace incomemenage=. if incomemenage==0
			xtile incomemenage_cat = incomemenage, n(3)
			
			replace incomepersonal=. if incomepersonal==0
			xtile incomepersonal_cat = incomepersonal, n(3)
			
			gen incomemenage_catt="Low Income" if incomemenage_cat==1
			replace incomemenage_catt="Middle Income" if incomemenage_cat==2
			replace incomemenage_catt="High Income" if incomemenage_cat==3
			
			gen incomepersonal_catt="Low Income" if incomepersonal_cat==1
			replace incomepersonal_catt="Middle Income" if incomepersonal_cat==2
			replace incomepersonal_catt="High Income" if incomepersonal_cat==3
			
			drop incomepersonal_cat incomemenage_cat
			ren incomemenage_catt incomemenage_cat
			ren incomepersonal_catt incomepersonal_cat
			

// convert to string
	foreach var of varlist gender incomepersonal_cat education_cat age_range urban {
		cap decode `var', gen(temp)
		if _rc == 0 {
			drop `var'
			ren temp `var'
		}
		ren `var' cut_`var'

	}

	// prep outcome vars
	
		
	gen i = _n
	
	tempfile reshaped reshaped
	drop bigchal_resolve_contact
	rename bigchal_resolve_contact__96 bigchal_resolve_contact_96
	
	reshape long cut_ , i(i) j(cuttype) string // reshape to case-cut level
	 drop i 
	gen i = _n
	
    reshape long bigchal_resolve_, i(i) j(resolve)
	replace resolve =3 if resolve>3 & resolve!=.
	
	label define actiontaken 0"Took no Action" 1 "Contacted agent" 2"Contacted provider" 3"Other"
	label values resolve actiontaken
 
**# Bookmark #3
	ren cut_ cut
	drop if cut==""
	/*
	replace cuttype = "Age" if cuttype == "age_range"
	replace cuttype = "Education" if cuttype == "education_cat"
	replace cuttype = "Gender" if cuttype == "gender"
	replace cuttype = "Income of the head" if cuttype == "incomepersonal_cat"
	replace cuttype = "Income of the household" if cuttype == "incomemenage_cat"
	*/ 
	*Brian made some changes here, I also included urbanity in the cuttypes**
	
	replace cuttype = "Age" if cuttype == "age_range"
	replace cuttype = "Education" if cuttype == "education_cat"
	replace cuttype = "Gender" if cuttype == "gender"
	replace cuttype = "Income" if cuttype == "incomepersonal_cat"
	
	collapse (mean) mean=bigchal_resolve_ (count) n=bigchal_resolve_  (sd) sd=bigchal_resolve_, by(cuttype cut resolve)									
		
		
		gen lo = (mean - invnormal(0.95)*sd/(n)^.5)
		gen hi = (mean + invnormal(0.95)*sd/(n)^.5) 
		
	
		export excel using "$redress\resolve_action_segmented", firstrow(varl) replace
		restore
		

***************Channel used to resolve challenge
		preserve
		
		keep caseid bigchal_resolve_contact_*
		rename bigchal_resolve_contact__96 bigchal_resolve_contact_96
		label define resolvecontact 1"In-person" 2"Via friend/family" 3"Phone call" 4"Social media" 5"Email" 6"Playstore" 7"Third party" 96"other"
		reshape long bigchal_resolve_contact_, i(caseid) j(resolvecontact)
		label values resolvecontact resolvecontact
		
		collapse (mean) bigchal_resolve_contact_, by(resolvecontact)
		
		export excel "$redress\resolve_challenge_channel", firstrow(variables) replace
		restore
		
**Outcome based on channnel use***

        preserve
		
		keep caseid bigchal_resolve_contact_* bigchal_result
		rename bigchal_resolve_contact__96 bigchal_resolve_contact_96
		label define resolvecontact 1"In-person" 2"Via friend/family" 3"Phone call" 4"Social media" 5"Email" 6"Playstore" 7"Third party" 96"other"
		reshape long bigchal_resolve_contact_, i(caseid) j(resolvecontact)
		replace resolvecontact=2 if  resolvecontact==7
		label define resolvecontact 2"Other", modify
		drop if bigchal_resolve_contact==.
		label values resolvecontact resolvecontact
				
		collapse (mean) bigchal_resolve_contact_  , by(bigchal_result resolvecontact)
		keep if bigchal_resolve_contact_!=0
		export excel "$redress\resolve_chal_channel_result", firstrow(variables) replace
		restore


		
	

***************What was the result of the action
		preserve
		contract bigchal_result, nomiss percent(Percent)
		replace Percent=Percent/100
		//export excel "$redress\resolve_challenge_results", firstrow(variables) replace
		restore	

	
***************Segment outcome based on amounts los
		
				
		preserve
		gen loss_cat= . 
		replace loss_cat=1 if bigchal_lossamt<=3000
		replace loss_cat=2 if bigchal_lossamt >3000 & bigchal_lossamt<=6000
		replace loss_cat=3 if bigchal_lossamt >6000 & bigchal_lossamt!=.
		
		label define loss 1"<=3000" 2">3000 & <=60000" 3 ">60000"
		lab val loss_cat loss
		
		contract bigchal_result loss_cat
		keep if loss_cat!=.
					
		export excel using "$redress\result_by_amountlost", firstrow(varl) replace
		restore

***************Segment usage pattern based on outcome of action taken (resolved/unresolved)		

preserve
gen bigchalresult=0 if bigchal_result==0 | bigchal_result==2
replace bigchalresult=1 if bigchal_result==1
label define resolve 1"Resolved" 0"Unresolved" 
label val bigchalresult resolve
contract bigchal_effect bigchalresult
drop if bigchal_effect==.
export excel using "$redress\usagepattern_by_result", firstrow(varl) replace
restore


**Who do consumers say is responsible for their biggest challenge? 

preserve
contract bigchal_responsible, nomiss percent(percent)
export excel using "$redress\bigchallresponsible", firstrow(varl) replace
restore










